﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Tongji_ZiJin]
	(
		@CompanyId int,
		@Today nchar(10)
	)
AS
Begin
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY c.Id ASC)AS Row
	,c.Id 
	,c.CompanyName
	,ISNULL((Select IBA From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBA--实有现金
	,ISNULL((Select IBB From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBB--实有银行
	                                                                                                                            --
	,ISNULL((Select Top 1 IBD From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBD--应有现金
	,ISNULL((Select Top 1 IBE From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBE--应有银行
	,ISNULL((Select IBC From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBC--账户余额	                                                                                                                                  
	,ISNULL((Select Top 1 IBF From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBF--借款合计
	,ISNULL((Select Top 1 IBG From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBG--资金合计                                                                                                                                  
	,ISNULL((Select Sum(IBH) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBH--应有现金平整	                                                                                                                                  
	,ISNULL((Select Sum(IBI) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBI--应有银行平整	                                                                                                                                  
	,ISNULL((Select (IBA+IBB)-(IBD+IBE) From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBJ--差额
	,ISNULL((Select IBK From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBK--应收合计
	,ISNULL((Select IBL From Statistics_Financial Where CompanyId=c.Id and Convert(nchar(10),CreateDate,120)=@Today),0) As IBL--应付合计	                                                                                                                                            	                                                                                                                                  --	                                                                                                                                                                                                                                                              
	From Company c Where (@CompanyId=0 Or Id=@CompanyId)
	)
	Select * From list,(SELECT SUM(IBA) AS IBA_a,SUM(IBB) AS IBB_a,SUM(IBD) AS IBD_a,SUM(IBE) AS IBE_a
	,SUM(IBC) AS IBC_a,SUM(IBF) AS IBF_a,SUM(IBG) AS IBG_a,SUM(IBH) AS IBH_a
	,SUM(IBI) AS IBI_a,SUM(IBJ) AS IBJ_a,SUM(IBK) AS IBK_a,SUM(IBL) AS IBL_a	
	 FROM list) AS n
End
